<?php
/**
 * Created by PhpStorm.
 * User: tao
 * Date: 8/2/2018
 * Time: 3:55 PM
 */


class ExcelUtil
{
    public $fileName = 'fileName_';
    public $fileType = 'xlsx';
    public $sheetName = 'sheet1';
    public $isHeader = true;
    public $excel = null;

    public function __construct($fileName, $sheetName = 'sheet1', $fileType = 'xlsx') {
        $this->fileName = $fileName;
        $this->fileType = $fileType;
        $this->sheetName = $sheetName;
        $this->excel = new PHPExcel();
    }

    /**
     * @param $fileName
     */
    public function setFileName($fileName) {
        if (!empty($fileName))
            $this->fileName = $fileName;
    }

    /**
     * @param $fileType
     */
    public function setFileType($fileType) {
        if (!empty($fileType))
            $this->fileType = $fileType;
    }

    /**
     * @param $sheetName
     */
    public function setSheetName($sheetName) {
        if (!empty($sheetName))
            $this->sheetName = $sheetName;
    }
    /**
     * 是否需要表头
     * @param $isHeader
     */
    public function setIsHeader($isHeader) {
        if (!empty($isHeader))
            $this->isHeader = $isHeader;
    }

    /**
     * @desc $data
     * @param array $data 需要遍历的数据
     * @param array $field data中某条数据的所有key数组
     * @param array $columnName 列名数组如果为空 则使用field填充
     * @throws PHPExcel_Exception
     * @throws PHPExcel_Reader_Exception
     * @throws PHPExcel_Writer_Exception
     */
    public function export($data, $field, $columnName = array()) {

        if(!is_array($columnName) || empty($columnName) || count($field) != count($columnName)) {
            $columnName = $field;
        }

        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator('dandelion')
            ->setLastModifiedBy('dandelion')
            ->setTitle($this->sheetName)
            ->setSubject($this->sheetName);

        //设置列的宽度，第一行加粗居中
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($this->sheetName);

        $sheet = $objPHPExcel->getActiveSheet();

        $letter = $this->letter();
        for($i = 0; $i < count($field); $i++) {
            $sheet->getColumnDimension($letter[$i])->setWidth(22);
            $sheet->getStyle($letter[$i].'1')->getFont()->setBold(true);
            $sheet->getStyle($letter[$i].'1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            $sheet->setCellValue($letter[$i].'1', ''.$columnName[$i]);
        }
        if(count($data) > 0 )
        {
            for($i = 0; $i < count($data); $i++) {
                for($j = 0; $j < count($field); $j++) {
                    //$sheet->setCellValue($letter[$j].''.($i+2), ''.$data[$i][$field[$j]]);
                    $sheet->setCellValueExplicit($letter[$j].''.($i+2), ''.$data[$i][$field[$j]], $pDataType = PHPExcel_Cell_DataType::TYPE_STRING);
                }
            }
        } else {
            $sheet->mergeCells('A2:'.$letter[count($field)-1].'2')
                ->getStyle('A2:'.$letter[count($field)-1].'2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle('A2:'.$letter[count($field)-1].'2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $sheet->setCellValue('A2', '暂无数据');
        }

        ob_end_clean();
        if (strtolower($this->fileType) == 'xls') {
            $newName = $this->fileName.'_'.time().'.'.'xls';
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$newName.'"');
            header('Cache-Control: max-age=0');
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        } else {
            $newName = $this->fileName.'_'.time().'.'.'xlsx';
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment; filename="'.$newName.'"');
            header('Cache-Control: max-age=0');
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        }
        $objWriter->save('php://output');
    }

    public static function import($file) {
        $objPHPExcel = new PHPExcel();
        $arr = array();
        if (!empty($file)) {
            try {
                $inputFileType = PHPExcel_IOFactory::identify($file);
                $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                $objPHPExcel = $objReader->load($file);
            } catch(Exception $e) {
                die('加载文件发生错误："'.pathinfo($file,PATHINFO_BASENAME).'": '.$e->getMessage());
            }
            $sheetCount = $objPHPExcel->getSheetCount();
            for ($i = 0; $i < $sheetCount; $i++) {
                $sheet = $objPHPExcel->getSheet($i);
                $rows = $sheet->getHighestRow();
                $columns = $sheet->getHighestColumn();

                for ($row = 1; $row <= $rows; $row++) {
                    $rowData = $sheet->rangeToArray('A' . $row . ':' . $columns . $row, NULL, TRUE, FALSE);
                    //这里得到的rowData都是一行的数据，得到数据后自行处理，我们这里只打出来看看效果
                    //var_dump($rowData);
                    $arr[$i][] = $rowData[0];
                }
            }
        }
        
        return $arr;
    }

    private function letter() {
        $letter = array(
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
            'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ',
            'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ',
            'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ',
            'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ', 'DR', 'DS', 'DT', 'DU', 'DV', 'DW', 'DX', 'DY', 'DZ',
            'EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG', 'EH', 'EI', 'EJ', 'EK', 'EL', 'EM', 'EN', 'EO', 'EP', 'EQ', 'ER', 'ES', 'ET', 'EU', 'EV', 'EW', 'EX', 'EY', 'EZ'
        );
        return $letter;
    }
}